/*
 *  This is a web service created for doing all the tasks associated with Archive Organize and Share notes on android
 */
package com.aoasws;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

import com.userrequestdaos.CreateCategoryDAO;
import com.userrequestdaos.FileInfoStoringDAO;
import com.userrequestdaos.LoginValidationDAO;
import com.userrequestdaos.RetrivingNotesDAO;
import com.userrequestdaos.RetrivingTagCountDAO;
import com.userrequestdaos.UserRegistrationDAO;
import com.userrequestdaos.ViewingCategoryAndTitleDAO;
import com.userrequestdaos.ViewingTitlesDAO;
import com.userresponsedtos.SendingResponseToClientDTO;

@WebService
public class ArchiveOraganizeShareWS {

	// declaring database connection fields
	static Connection conn;
	static Statement stmt;
	ResultSet rs;
	String response="<root><verifycheck>",res="";
	SendingResponseToClientDTO obj_sendingresponse;
	/*
	 *  The static block consists of database connection logic
	 */
	//	static
	//	{
	//		try
	//		{         
	//			Class.forName("com.mysql.jdbc.Driver");
	//			System.out.println("Driver Loaded");
	//			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/aoasdb", "root", "root");
	//			System.out.println("Connection established to database");
	//		}
	//		catch (Exception e) 
	//		{      
	//			e.printStackTrace();
	//			System.out.println("Unable to connect to database");
	//		} 
	//	}

	/*
	 *  Opening Connection
	 */
	public void openDBConnection()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Driver Loaded");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/aoasdb", "root", "root");
			System.out.println("Connection established to database");
			stmt=conn.createStatement();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	/*
	 *   Closing connection
	 */
	public void closeDBConnection()
	{
		try
		{
			stmt.close();
			rs.close();
			conn.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	/*
	 *   Login validation method
	 */
	@WebMethod
	public SendingResponseToClientDTO userLoginValidation(@WebParam(name="obj_loginvalidation")LoginValidationDAO obj_loginvalidation)
	{ 
		try 
		{
			System.out.println(" "+obj_loginvalidation.getV_useremail()+" \n "+obj_loginvalidation.getV_userpassword());
			openDBConnection();
			rs=stmt.executeQuery("select firstname from registrationtb where emailid='"+obj_loginvalidation.getV_useremail()+"' and password='"+obj_loginvalidation.getV_userpassword()+"'");
			if(rs.next()) 
			{
				res="loginsucess";
			}
			else
			{   
				res="loginfailed";
			}
		}
		catch (Exception e) 
		{
			res="serverproblem";
			e.printStackTrace();
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</verifycheck></root>";
			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		// sending response to client
		return obj_sendingresponse;
	}

	/*
	 *  Method for registration  
	 */
	@WebMethod
	public SendingResponseToClientDTO userRegistration(@WebParam(name="obj_userregister")UserRegistrationDAO obj_userregister)
	{
		try
		{
			openDBConnection();
			rs=stmt.executeQuery("select emailid from registrationtb where emailid='"+obj_userregister.getV_useremail()+"' and password='"+obj_userregister.getV_userpassword()+"'");
			if(rs.next())
			{	
				res="useralreadyexists";
			}
			else
			{
				int insert_register=stmt.executeUpdate("insert into registrationtb values('"+obj_userregister.getV_fname()+"','"+obj_userregister.getV_lname()+"','"+obj_userregister.getV_useremail()+"','"+obj_userregister.getV_userpassword()+"','"+obj_userregister.getV_mobileno()+"')");
				if(insert_register==1)
				{
					res="registered sucessfully";
				}
				else
				{  
					res="unable to register";
				}
			}  
		}
		catch (Exception e) 
		{    
			e.printStackTrace();
			res="serverproblem";
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</verifycheck></root>";
			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}
	/*  
	 *  The following method will receive the files transfered information from 
	 *  android mobile and update those details in database
	 */
	@WebMethod
	public SendingResponseToClientDTO fileInfoSave(@WebParam(name="obj_fileinfosaving")FileInfoStoringDAO obj_fileinfosaving)
	{
		String filepath;
		try
		{ 
			openDBConnection();
			System.out.println("The received data is "+obj_fileinfosaving.getV_useremail() +" "+obj_fileinfosaving.getV_filetype()+"  "+obj_fileinfosaving.getV_filename()+"  "+obj_fileinfosaving.getV_categoryname()+"  "+obj_fileinfosaving.getV_title()+" "+obj_fileinfosaving.getV_content());
			/*
			 * connecting to table, verifying whether,
			 *  there is any data already present w.r.t title, 
			 * if yes alert the user else insert the data
			 */

			rs=stmt.executeQuery("select title from userfiles where emailid='"+obj_fileinfosaving.getV_useremail()+"' and category='"+obj_fileinfosaving.getV_categoryname()+"'and title='"+obj_fileinfosaving.getV_title()+"'");
			if(rs.next())
			{
				res="title_exists";
			}
			else  
			{ 
				//creating filepath    
				if(obj_fileinfosaving.getV_filename().equals("text"))
				{
					filepath=""; 
				}
				else 
				{ 
					filepath="http://10.0.2.2:88/archiveapplicationfiles/"+obj_fileinfosaving.getV_filename()+"";
				}
				// inserting data
				int insertdata=stmt.executeUpdate("insert into userfiles(emailid,filetype,filepath,date,category,title,content) values('"+obj_fileinfosaving.getV_useremail()+"','"+obj_fileinfosaving.getV_filetype()+"','"+filepath+"',sysdate(),'"+obj_fileinfosaving.getV_categoryname()+"','"+obj_fileinfosaving.getV_title()+"','"+obj_fileinfosaving.getV_content()+"')");
				if(insertdata==1)
				{
					res="inserted sucessfully";	
				}
				else
				{
					res="unable to insert";	
				}
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem";
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</verifycheck></root>";
			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}
	/*
	 *  creating categories w.r.t username
	 */
	@WebMethod
	public SendingResponseToClientDTO createUserCategories(@WebParam(name="obj_createcategory") CreateCategoryDAO obj_createcategory)
	{
		try
		{
			openDBConnection();
			rs=stmt.executeQuery("select categoryname from usercategories where categoryname='"+obj_createcategory.getV_categoryname()+"' and useremailid='"+obj_createcategory.getV_useremail()+"'");
			if(rs.next())
			{
				res="category already exists";
			}
			else
			{
				int insert_category=stmt.executeUpdate("insert into usercategories(categoryname,useremailid) values('"+obj_createcategory.getV_categoryname()+"','"+obj_createcategory.getV_useremail()+"')");
				if(insert_category==1)
				{
					res="created category";
				}
				else
				{
					res="unable to create category";
				}
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem";

		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</verifycheck></root>";
			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}

	/*
	 *   Method for viewing categories and titles w.r.t username
	 */
	@WebMethod
	public SendingResponseToClientDTO viewCategoriesAndTitles(@WebParam(name="obj_viewcategorytitle")ViewingCategoryAndTitleDAO obj_viewcategorytitle)
	{
		ResultSet rs1=null; 
		try
		{
			openDBConnection();
			rs=stmt.executeQuery("select categoryname from usercategories where useremailid='"+obj_viewcategorytitle.getV_useremail()+"'");
			if(rs.next())
			{
				rs.previous();
				// verification response
				res="datapresent</verifycheck>";
				while(rs.next())
				{  
					res+="<userdata categoryname=\""+rs.getString(1)+"\">";
					//+"+rs.getString(1)+">";
					String cat=rs.getString(1);
					Statement stmt1=conn.createStatement();
					rs1=stmt1.executeQuery("select title from userfiles where category='"+cat+"'");
					if(rs1!=null)
					{
						while(rs1.next())
						{
							res+="<titlename>"+rs1.getString(1)+"</titlename>";
						}

					}
					else
					{
						res+="<titlename>no title present</titlename>";
					}
					res+="</userdata>";
				}
				rs1.close();
			}
			else
			{
				res="nodata</verifycheck>";
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem</verifycheck>";	
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</root>";

			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}
	/*
	 *  Method for viewing categories
	 *  
	 */
	@WebMethod
	public SendingResponseToClientDTO viewCategories(@WebParam(name="obj_viewcategorytitle")ViewingCategoryAndTitleDAO obj_viewcategorytitle)
	{
		try
		{
			openDBConnection();
			if(obj_viewcategorytitle.getNotetype().equals("nonote"))
			{
				System.out.println("in nodata");
				rs=stmt.executeQuery("select categoryname from usercategories where useremailid='"+obj_viewcategorytitle.getV_useremail()+"'");	
			}
			else
			{
				System.out.println("in data"+obj_viewcategorytitle.getNotetype());
				rs=stmt.executeQuery("select category from userfiles where emailid='"+obj_viewcategorytitle.getV_useremail()+"' and filetype='"+obj_viewcategorytitle.getNotetype()+"'");
			}
			if(rs.next())
			{
				rs.previous();  
				// verification response
				res="datapresent</verifycheck>";
				res+="<userdata>";
				while(rs.next())
				{  

					res+="<categoryname>"+rs.getString(1)+"</categoryname>";
				}
				res+="</userdata>";
			}
			else
			{ 
				res="nodata</verifycheck>";
			}
			
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem</verifycheck>";	
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</root>";

			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse; 
	}

	/*
	 *  Method for viewing categories
	 *  
	 */
	@WebMethod
	public SendingResponseToClientDTO viewTitles(@WebParam(name="obj_viewtitle")ViewingTitlesDAO obj_viewtitle)
	{
		try
		{  
			openDBConnection();
			System.out.println("category='"+obj_viewtitle.getV_categoryname()+"' and emailid='"+obj_viewtitle.getV_useremail()+"' and filetype='"+obj_viewtitle.getV_notetype()+"' ");
			rs=stmt.executeQuery("select title from userfiles where category='"+obj_viewtitle.getV_categoryname()+"' and emailid='"+obj_viewtitle.getV_useremail()+"' and filetype='"+obj_viewtitle.getV_notetype()+"'");
			if(rs.next())
			{
				rs.previous();
				// verification response
				res="datapresent</verifycheck>";
				res+="<userdata>";
				while(rs.next())
				{  	
					res+="<titlename>"+rs.getString(1)+"</titlename>";
				}
				res+="</userdata>";   
			}
			else
			{
				res="nodata</verifycheck>";
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem</verifycheck>";	
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</root>";

			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse; 
	}
	/*
	 *  Method for sending the notes i.e, images or text notes or audio files
	 */
	@WebMethod
	public SendingResponseToClientDTO retrivingNotes(@WebParam(name="obj_retrivenotes")RetrivingNotesDAO obj_retrivenotes)
	{
		try 
		{ 
			System.out.println("Received Data is " +obj_retrivenotes.getV_useremail() +"   " +obj_retrivenotes.getV_categoryname()+"   "+obj_retrivenotes.getV_titlename());
			openDBConnection();
			rs=stmt.executeQuery("select filepath,content from userfiles where emailid='"+obj_retrivenotes.getV_useremail()+"' and category='"+obj_retrivenotes.getV_categoryname()+"' and title='"+obj_retrivenotes.getV_titlename()+"'");
			if(rs.next())
			{
				rs.previous();
				// verification response
				res="datapresent</verifycheck>";
				if(rs.next())
				{  
					res+="<userdata>";
					// for filepath
					res+="<filepath>"+rs.getString(1)+"</filepath>";
					// for content
					res+="<content>"+rs.getString(2)+"</content>";
					res+="</userdata>";
				}
			}
			else
			{
				res="nodata</verifycheck>";
			}
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem</verifycheck>";
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</root>";

			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}

	/*
	 *  This method is used for fetching the count w.r.t text,audio and image files
	 */
	@WebMethod
	public SendingResponseToClientDTO retriveTagCount(RetrivingTagCountDAO obj_retrivetagcount)
	{
		try
		{
			openDBConnection();
			rs=stmt.executeQuery("(select count(filetype) from userfiles where emailid='"+obj_retrivetagcount.getUseremail()+"' and filetype='audio' ) union (select count(filetype) from userfiles where emailid='"+obj_retrivetagcount.getUseremail()+"' and filetype='text' ) union (select count(filetype) from userfiles where emailid='"+obj_retrivetagcount.getUseremail()+"' and filetype='image')");
			if(rs.next())
			{
				rs.previous();
				// verification response
				res="datapresent</verifycheck>";
				if(rs.next())
				{  
					res+="<userdata>";
					// for text notes
					res+="<textnotes>"+rs.getString(1)+"</textnotes>";
					// for audio notes
					res+="<audionotes>"+rs.getString(2)+"</audionotes>";
					// for image notes
					res+="<imagenotes>"+rs.getString(3)+"</imagenotes>";
					res+="</userdata>";
				}
			}
			else
			{
				res="no</verifycheck>";
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
			res="serverproblem</verifycheck>";
		} 
		finally
		{
			// calling this method for closing the database connection
			closeDBConnection();
			response+=res+"</root>";

			System.out.println(response);
			// creating response object
			obj_sendingresponse=new SendingResponseToClientDTO(response);
		}
		return obj_sendingresponse;
	}
}






